
set more off
clear

putexcel set "${path}\Results\Summary Statistics\SCF", modify sheet(Sheet1)

******************************
* 2007 Survey 
******************************

use YY1 Y1 X14 X716 X3819 X3822 X3824 X3826 X3828 X3830 X7787 X3901 X3902 X3903 X3913 X3915 X8022 X7401 X7411  X905 X7402 X7412 X3727 X3730 X3736 X3742 X3748 X3754 X3760 X3501 X3506 X3510 X3514 X3518 X3522 X3526 X6551 X6552 X6559 X6560 X3721 X7635 X7637 X7636 X7638 X6706 X7639 X7401 X5729 X805 X1005 X1700 X1706 X1906 X1705 X1905 X1711 X1911 X1715 X1915 X4135 X4735 X11032 X11132 X11232 X11332 X11432 X11532 X4006 X5702 X5704 X5716 X5718 X5720 X5722 using "${path}\Raw Data\SCF\p07i6.dta"

gen Savings = 0
replace Savings = X3730 if X3730 > 0
replace Savings = Savings + X3736 if X3736 > 0 
replace Savings = Savings + X3742 if X3742 > 0 
replace Savings = Savings + X3748 if X3748 > 0 
replace Savings = Savings + X3754 if X3754 > 0 
replace Savings = Savings + X3760 if X3760 > 0 

gen Checking = 0
replace Checking = X3506 if X3506 > 0 
replace Checking = Checking + X3510 if X3510 > 0 
replace Checking = Checking + X3514 if X3514 > 0 
replace Checking = Checking + X3518 if X3518 > 0 
replace Checking = Checking + X3522 if X3522 > 0 
replace Checking = Checking + X3526 if X3526 > 0 

gen Bonds = 0
replace Bonds = X7635 if X7635 > 0 /* Mortgage backed bonds */
replace Bonds = Bonds + X7637 if X7637 > 0 /* Municipal bonds */
replace Bonds = Bonds + X7636 if X7636 > 0 /* Gov't bonds */
replace Bonds = Bonds + X7638 if X7638 > 0 /* Foreign bonds */
replace Bonds = Bonds + X7639 if X7639 > 0 /* Corporate bonds */

rename X3902 SavingsBonds

* Real Estate * 
gen HomeValue = X716 /* Primary residence */
gen Mortgage = X805 + X905 + X1005 /* Outstanding mortgage still owed */
gen Property = (X1705*X1706)/100 + X1905 /* Other real estate */
gen Mortgage2 = X1715 + X1915 /* Other mortgages */

gen NetRealEstate = HomeValue + Property - Mortgage - Mortgage2

gen IRA = X6551 + X6552 + X6559 + X6560
replace IRA = 0 if IRA < 0

gen CD = X3721
replace CD = 0 if CD < 0

gen Stocks = X3915
replace Stocks = 0 if Stocks < 0

gen Pensions = X11032 + X11132 + X11232 + X11332 + X11432 + X11532
replace Pensions = 0 if Pensions < 0

gen LifeInsurance = 0
replace LifeInsurance =  X4006 if X4006 > 0

gen Income = X5702 + X5704 + X5716 + X5718 + X5720+ X5722 /* Non-financial income */
replace Income = 0 if Income < 0
replace Income = Income/12

gen Funds = 0
replace Funds = X3822 if X3822 > 0 
replace Funds = Funds + X3824 if X3824 > 0 
replace Funds = Funds + X3826 if X3826 > 0 
replace Funds = Funds + X3828 if X3828 > 0 
replace Funds = Funds + X3830 if X3830 > 0 
replace Funds = Funds + X7787 if X7787 > 0 

* Definitions come from Kaplan, Violante, and Weidner (2014) * 
gen Liquid = Savings + Checking + Funds + Stocks + Bonds 
gen Illiquid = NetRealEstate + IRA + CD + SavingsBonds + Pensions + LifeInsurance

gen Asset = Liquid + Illiquid


*
gen occ = .
replace occ = 1 if X7401 == 1 /* Abstract occupations */
replace occ = 2 if X7401 == 5 | X7401 == 2 /* Routine occupations */
replace occ = 3 if X7401 == 3 | X7401 == 4 /* Manual occupations */


rename X14 Age

gen PHTM = 0
replace PHTM = 1 if Liquid < Income/2 & Illiquid <= 1000

gen WHTM = 0
replace WHTM = 1 if Liquid < Income/2 & Illiquid > 1000

merge m:1 YY1 using "${path}\Raw Data\SCF\weights07.dta"

* The SCF has a very complicated weighting scheme. See documentation for details.

keep if Age >= 25 & Age <= 65

foreach var in Liquid Asset Income Age  {
    
	forvalues i = 1/3 {
    
	gen `var'wgtmed`i' = .
	
		quietly forvalues x = 1/999 {
	    
		sum `var' [w=WT1B`x'] if occ == `i', detail
		replace `var'wgtmed`i' = r(p50) in `x'
		
		}
	
	}
	
}

forvalues i = 1/3 {

	gen PerWHTM`i' = .

	quietly forvalues x = 1/999 {
	    
			sum WHTM [w=WT1B`x'] if occ == `i', detail
			replace PerWHTM`i' = r(mean) in `x'
		
		}

		
	gen PerPHTM`i' = .

	quietly forvalues x = 1/999 {
	    
			sum PHTM [w=WT1B`x'] if occ == `i', detail
			replace PerPHTM`i' = r(mean) in `x'
		
		}

}


forvalues occ = 1/3 {
    
	tabstat Liquidwgtmed`occ', stat(mean sd) save

	mat A`occ' = r(StatTotal)

	tabstat Assetwgtmed`occ', stat(mean sd)  save

	mat B`occ' = r(StatTotal)
	
	tabstat Incomewgtmed`occ', stat(mean sd)  save

	mat C`occ' = r(StatTotal)
	
	tabstat Agewgtmed`occ', stat(mean sd)  save

	mat D`occ' = r(StatTotal)
	
	tabstat PerWHTM`occ', stat(mean sd)  save

	mat E`occ' = r(StatTotal)
	
	tabstat PerPHTM`occ', stat(mean sd)  save

	mat F`occ' = r(StatTotal)
	
}

mat Liquid = [A1, A2, A3] 
mat Asset = [B1, B2, B3]
mat Income = [C1, C2, C3]
mat Age = [D1, D2, D3]
mat WHTM = [E1, E2, E3]
mat PHTM = [F1, F2, F3]

putexcel J7 = matrix(Liquid)
putexcel J9 = matrix(Asset)
putexcel J11 = matrix(Income)
putexcel J13 = matrix(Age)
putexcel J15 = matrix(WHTM)
putexcel J17 = matrix(PHTM)


************************************
* 1995 Survey
************************************

clear

use YY1 Y1 X14 X716 X3804 X3807 X3810 X3813 X3816 X3819 X3822 X3824 X3826 X3828 X3830 X3901 X3902 X3903 X3913 X3915 X8022 X7401 X7411 X7402 X7412 X3501 X3506 X3510 X3514 X3518 X3522 X3526 X3721 X7635 X7637 X7636 X7638 X6706 X7639 X7401 X3610 X3620 X3630 X5729 X805 X905 X1005 X1700 X1706 X1906 X1705 X1905 X1711 X1911 X1715 X1915 X4135 X4735 X4006 X5702 X5704 X5716 X5718 X5720 X5722 X4204 X4304 X4404 X4804 X4904 X5004 using "${path}\Raw Data\SCF\p95i6.dta"

gen IRA = X3610
replace IRA = IRA + X3620 + X3630

gen Checking = .
replace Checking = X3506 if X3506 > 0 
replace Checking = Checking + X3510 if X3510 > 0 
replace Checking = Checking + X3514 if X3514 > 0 
replace Checking = Checking + X3518 if X3518 > 0 
replace Checking = Checking + X3522 if X3522 > 0 
replace Checking = Checking + X3526 if X3526 > 0 
replace Checking = 0 if Checking == .

gen Savings = X3804 if X3804 > 0 
replace Savings = Savings + X3807 if X3807 > 0
replace Savings = Savings + X3810 if X3810 > 0
replace Savings = Savings + X3813 if X3813 > 0
replace Savings = Savings + X3816 if X3816 > 0 
replace Savings = 0 if Savings == .

* Real Estate * 
gen HomeValue = X716 /* Primary residence */
gen Mortgage = X805 + X905 + X1005 /* Outstanding mortgage still owed */
gen Property = (X1705*X1706)/100 + X1905 /* Other real estate */
gen Mortgage2 = X1715 + X1915 /* Other mortgages */

gen NetRealEstate = HomeValue + Property - Mortgage - Mortgage2

gen LifeInsurance = 0
replace LifeInsurance =  X4006 if X4006 > 0

gen Pensions = 0
replace Pensions = X4204 + X4304 + X4404 + X4804 + X4904 + X5004

rename X3721 CD 
replace CD = 0 if CD < 0

gen Funds = 0
replace Funds = X3822 if X3822 > 0 
replace Funds = Funds + X3824 if X3824 > 0 
replace Funds = Funds + X3826 if X3826 > 0 
replace Funds = Funds + X3828 if X3828 > 0 
replace Funds = Funds + X3830 if X3830 > 0 

gen Bonds = 0
replace Bonds = X7635 if X7635 > 0
replace Bonds = Bonds + X7637 if X7637 > 0 
replace Bonds = Bonds + X7636 if X7636 > 0 
replace Bonds = Bonds + X7638 if X7638 > 0 
replace Bonds = Bonds + X7639 if X7639 > 0

gen SavingsBonds = X3902

gen Stocks = 0
replace Stocks = X3915 

rename X14 Age
*
gen occ = .
replace occ = 1 if X7401 == 1 /* Abstract occupations */
replace occ = 2 if X7401 == 5 | X7401 == 2 /* Routine occupations */
replace occ = 3 if X7401 == 3 | X7401 == 4 /* Manual occupations */


gen Income = X5702 + X5704 + X5716 + X5718 + X5720+ X5722 /* Non-financial income */
replace Income = 0 if Income < 0
replace Income = Income/12

gen Liquid = Savings + Checking + Funds + Stocks + Bonds 
gen Illiquid = NetRealEstate + IRA + CD + SavingsBonds + Pensions + LifeInsurance

gen Asset = Liquid + Illiquid

gen PHTM = 0
replace PHTM = 1 if Liquid < Income/2 & Illiquid <= 1000

gen WHTM = 0
replace WHTM = 1 if Liquid < Income/2 & Illiquid > 1000

keep if Age >= 25 & Age <= 65

merge m:1 YY1 using "${path}\Raw Data\SCF\weights95.dta"

foreach var in Liquid Asset Income Age {
    
	forvalues i = 1/3 {
    
	gen `var'wgtmed`i' = .
	
		quietly forvalues x = 1/999 {
	    
		sum `var' [w=WT1B`x'] if occ == `i', detail
		replace `var'wgtmed`i' = r(p50) in `x'
		
		}
	
	}
	
}
forvalues i = 1/3 {

	gen PerWHTM`i' = .

	quietly forvalues x = 1/999 {
	    
			sum WHTM [w=WT1B`x'] if occ == `i', detail
			replace PerWHTM`i' = r(mean) in `x'
		
		}

		
	gen PerPHTM`i' = .

	quietly forvalues x = 1/999 {
	    
			sum PHTM [w=WT1B`x'] if occ == `i', detail
			replace PerPHTM`i' = r(mean) in `x'
		
		}

}

forvalues occ = 1/3 {
    
	tabstat Liquidwgtmed`occ', stat(mean sd) save

	mat A`occ' = r(StatTotal)

	tabstat Assetwgtmed`occ', stat(mean sd)  save

	mat B`occ' = r(StatTotal)
	
	tabstat Incomewgtmed`occ', stat(mean sd)  save

	mat C`occ' = r(StatTotal)
	
	tabstat Agewgtmed`occ', stat(mean sd)  save

	mat D`occ' = r(StatTotal)
	
	tabstat PerWHTM`occ', stat(mean sd)  save

	mat E`occ' = r(StatTotal)
	
	tabstat PerPHTM`occ', stat(mean sd)  save

	mat F`occ' = r(StatTotal)
	
}

mat Liquid = [A1, A2, A3] 
mat Asset = [B1, B2, B3]
mat Income = [C1, C2, C3]
mat Age = [D1, D2, D3]
mat WHTM = [E1, E2, E3]
mat PHTM = [F1, F2, F3]

putexcel F7 = matrix(Liquid)
putexcel F9 = matrix(Asset)
putexcel F11 = matrix(Income)
putexcel F13 = matrix(Age)
putexcel F15 = matrix(WHTM)
putexcel F17 = matrix(PHTM)


putexcel close